using System;
using System.Collections.Generic;
using WYJCore.Extension;
using System.Linq;
using System.Data.SqlClient;
using System.Text;
using WYJCore;
using System.Reflection;
using System.Linq.Expressions;
using WYJWeb;

namespace WYJData.Orm
{
    public class Table
    {
        public string TableName { get; set; }
        public Type ModelType { get; set; }
        public List<Column> Columns { get; set; } = new List<Column>();

        private void Init(Type modelType)
        {
            if (!modelType.IsModel()) throw new WYJException($"{modelType.FullName}不是有效的实体类型");
            modelType.GetProperties().ForEach(prop =>
            {
                if (prop.GetCustomAttribute<ColumnAttribute>() is ColumnAttribute attr) Columns.Add(new Column(attr, prop));
            });
            ModelType = modelType;
        }

        public Table(Type modelType, string pretext = "T_")
        {
            TableName = pretext + modelType.Name;
            Init(modelType);
        }
        public Table(object model, string pretext = "T_")
        {
            var modelType = model.GetType();
            TableName = pretext + modelType.Name;
            Init(modelType);

        }
        public void CreateDbTable(SqlCommand sqlCommand)
        {
            var sb = new StringBuilder().AppendFormat(@"IF NOT EXISTS (SELECT id FROM sysobjects WHERE id = object_id('{0}')
                AND OBJECTPROPERTY(id, 'IsUserTable') = 1) 
                CREATE TABLE [dbo].{0}(", TableName)
                .AppendJoin(",", Columns.Select(x => x.CreateSql(TableName, SqlClipType.Create)));
            var sql = sb.Append(")").ToString();
            sqlCommand.Execute(sql);
        }

        public object ToModel(SqlDataReader dataReader)
        {
            var model = Activator.CreateInstance(ModelType);
            Columns.ForEach(column =>
            {
                if (column.ColumnType == ColumnType.Model)
                {
                    var table = column.Table;
                    var subModel = Activator.CreateInstance(table.ModelType);
                    table.Columns.ForEach(col =>
                    {
                        if (col.ColumnType == ColumnType.Model)
                        {
                            var value = dataReader[col.Ordinal];
                            if (value != DBNull.Value) { 
                                var m = Activator.CreateInstance(col.ColumnInfo.PropertyType) as IModel;
                                m.Id = (int)value;
                                if (!m.IsNull()) col.ColumnInfo.SetValue(subModel, m);                            
                            };

                        }
                        else
                        {
                            var value = dataReader[col.Ordinal];
                            if (value != DBNull.Value) col.ColumnInfo.SetValue(subModel, value);
                        }
                    });
                    if (!(subModel as IModel).IsNull()) column.ColumnInfo.SetValue(model, subModel);
                }
                else
                {
                    var value = dataReader[column.Ordinal];
                    if (value != DBNull.Value) column.ColumnInfo.SetValue(model, value);
                }
            });
            return model;
        }

        public string CreateSql(IModel model, SqlClipType sqlType)
        {
            return sqlType switch
            {
                SqlClipType.Insert => GetInsertSql(model),
                SqlClipType.Update => GetUpdateSql(model),
                _ => CreateQuerySql().ToString(),
            };
        }

        public StringBuilder CreateQuerySql()
        {
            var select = new StringBuilder("SELECT ");
            var from = new StringBuilder(" FROM ").Append(TableName);
            var ordinal = 0;
            Columns.ForEach(column =>
            {
                if (column.ColumnType == ColumnType.Model)
                {
                    var table = new Table(column.ColumnInfo.PropertyType);
                    table.Columns.ForEach(tColumn =>
                    {
                        tColumn.Ordinal = ordinal++;
                        select.AppendFormat("{0}.[{1}],", table.TableName, tColumn.ColumnName);
                    });
                    column.Table = table;
                    from.AppendFormat(" LEFT JOIN {0} ON {1}.[{2}] = {0}.Id ", table.TableName, TableName, column.ColumnName);
                }
                else
                {
                    column.Ordinal = ordinal++;
                    select.AppendFormat("{0}.[{1}],", TableName, column.ColumnName);
                }
            });
            select.Remove(select.Length - 1, 1);//循环结束后删除最后一个逗号。
            return select.Append(from);
        }

        public string GetInsertSql(IModel Model)
        {
            var columns = Columns.Where(column => !column.Attribute.Identity);
            var columnValues = columns.Select(column => column.TransValue(Model));
            return $"INSERT INTO {TableName} ({columns.JoinToString()}) VALUES ({columnValues.JoinToString()}) ";
        }
        public string GetUpdateSql(IModel Model)
        {
            var columns = Columns.Where(column => !column.Attribute.Identity);
            var set = columns.Select(col => $"{col.ColumnName} = {col.TransValue(Model)}");
            return $"UPDATE {TableName} SET {set.JoinToString()} WHERE Id = {Model.Id} ";
        }

    }
}
